﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data.Oracle;
using System.Data;
using System.Data.OracleClient;

namespace Sinacor.Infra.Tools
{
    public class MenuRegistration
    {
        DbConnection cnn = null;

        private void CloseConnection()
        {
            cnn.Close();
            cnn.Dispose();
        }

        private void OpenConnection()
        {
            OracleDatabase db = new OracleDatabase(ConfigurationSettings.AppSettings["connectionString"].ToString());
            cnn = db.CreateConnection();
            cnn.Open();
        }

        public DataSet BuscaModulos()
        {
            StringBuilder sql = new StringBuilder(); ;
            DataSet ds = new DataSet();

            OpenConnection();

            DbCommand cmd = cnn.CreateCommand();

            sql.Append("SELECT CD_SISTEMA FROM TFWMODULOSINST ORDER BY CD_SISTEMA");

            cmd.CommandText = sql.ToString();

            IDbDataAdapter dataAdapter = new OracleDataAdapter();
            dataAdapter.SelectCommand = cmd;
            dataAdapter.Fill(ds);

            CloseConnection();

            return ds;
        
        }

        public DataSet BuscaMenuSuperior(string codModulo)
        {
            StringBuilder sql = new StringBuilder(); ;
            DataSet ds = new DataSet();

            OpenConnection();

            DbCommand cmd = cnn.CreateCommand();

            sql.Append(@"SELECT CD_MENU, DS_MENU || ' [' || TO_CHAR(CD_MENU) || ']' ""DS_MENU"" FROM TFWMENU WHERE CD_SISTEMA = '" + codModulo + "' ORDER BY DS_MENU");

            cmd.CommandText = sql.ToString();

            IDbDataAdapter dataAdapter = new OracleDataAdapter();
            dataAdapter.SelectCommand = cmd;
            dataAdapter.Fill(ds);

            CloseConnection();

            return ds;

        }

        public DataSet BuscaMenus(int codMenu, string descricao, int menuSuperior, string funcao, string modulo, string classe, string assembly, string bloqueio)
        {
            StringBuilder sql = new StringBuilder(); ;
            DataSet ds = new DataSet();

            OpenConnection();

            DbCommand cmd = cnn.CreateCommand();

            sql.Append(@"SELECT M.CD_MENU, M.CD_MENUSUP, M.CD_FUNCAO, M.CD_SISTEMA, M.DS_ASSEMBLY, M.DS_CLASSE, M.IN_BLOQUEIO, M.DS_MENU, M.CD_FUNCAO || ' - ' || F.NM_FUNCAO ""NM_FUNCAO"", ME.DS_MENU ""DS_MENUSUP""
                        FROM TFWMENU M 
                        INNER JOIN TGEFUNCAO F ON M.CD_FUNCAO = F.CD_FUNCAO AND M.CD_SISTEMA = F.CD_SISTEMA
                        LEFT JOIN TFWMENU ME ON M.CD_MENUSUP = ME.CD_MENU
                        WHERE 1 = 1");

            if (!String.IsNullOrEmpty(descricao))
                sql.Append(@" AND UPPER(M.DS_MENU) LIKE '%" + descricao.ToUpper() + "%'");

            if (menuSuperior != 0)
                sql.Append(@" AND M.CD_MENUSUP = " + menuSuperior.ToString() + "");

            if (codMenu != 0)
                sql.Append(@" AND M.CD_MENU = " + codMenu.ToString() + "");

            if (!String.IsNullOrEmpty(funcao))
                sql.Append(@" AND M.CD_FUNCAO = '" + funcao.ToString() + "'");

            if (!String.IsNullOrEmpty(modulo))
                sql.Append(@" AND M.CD_SISTEMA = '" + modulo.ToString() + "'");

            if (!String.IsNullOrEmpty(classe))
                sql.Append(@" AND UPPER(M.DS_CLASSE) LIKE '%" + classe.ToUpper() + "%'");

            if (!String.IsNullOrEmpty(assembly))
                sql.Append(@" AND UPPER(M.DS_ASSEMBLY) LIKE '%" + assembly.ToUpper() + "%'");

            if (!String.IsNullOrEmpty(bloqueio))
                sql.Append(@" AND M.IN_BLOQUEIO = '" + bloqueio.ToString() + "'");

            sql.Append(@" ORDER BY M.DS_MENU, M.DS_CLASSE"); //inc - esertorio - 200810171200

            cmd.CommandText = sql.ToString();

            IDbDataAdapter dataAdapter = new OracleDataAdapter();
            dataAdapter.SelectCommand = cmd;
            dataAdapter.Fill(ds);

            CloseConnection();

            return ds;
        }

        public void IncluirMenu(string descricao, int menuSuperior, string funcao, string modulo, string classe, string assembly, string bloqueio)
        {
            StringBuilder sql = new StringBuilder(); ;
            DataSet ds = new DataSet();

            OpenConnection();

            DbCommand cmd = cnn.CreateCommand();

            sql.Append(@"INSERT INTO TFWMENU(
                          CD_MENU,
                          CD_MENUSUP,
                          CD_FUNCAO, 
                          CD_SISTEMA, 
                          DS_ASSEMBLY,                                                                                          
                          DS_CLASSE,                                                                                            
                          IN_BLOQUEIO, 
                          DS_MENU)
                        VALUES
                        (
                          (SELECT MAX(CD_MENU) + 1 FROM TFWMENU),
                          :CD_MENUSUP,
                          :CD_FUNCAO, 
                          :CD_SISTEMA, 
                          :DS_ASSEMBLY,                                                                                          
                          :DS_CLASSE,                                                                                            
                          :IN_BLOQUEIO, 
                          :DS_MENU
                        )");


            DbParameter paramMenuSup = cmd.CreateParameter();
            paramMenuSup.ParameterName = "CD_MENUSUP";
            paramMenuSup.DbType = DbType.Int32;

            if (menuSuperior != 0)
                paramMenuSup.Value = menuSuperior;
            else
                paramMenuSup.Value = DBNull.Value;

            DbParameter paramFuncao = cmd.CreateParameter();
            paramFuncao.ParameterName = "CD_FUNCAO";
            paramFuncao.DbType = DbType.String;
            paramFuncao.Value = funcao;

            DbParameter paramSistema = cmd.CreateParameter();
            paramSistema.ParameterName = "CD_SISTEMA";
            paramSistema.DbType = DbType.String;
            paramSistema.Value = modulo;

            DbParameter paramAssembly = cmd.CreateParameter();
            paramAssembly.ParameterName = "DS_ASSEMBLY";
            paramAssembly.DbType = DbType.String;
            paramAssembly.Value = assembly;

            DbParameter paramClasse = cmd.CreateParameter();
            paramClasse.ParameterName = "DS_CLASSE";
            paramClasse.DbType = DbType.String;
            paramClasse.Value = classe;

            DbParameter paramBloqueio = cmd.CreateParameter();
            paramBloqueio.ParameterName = "IN_BLOQUEIO";
            paramBloqueio.DbType = DbType.String;
            paramBloqueio.Value = bloqueio;

            DbParameter paramDescricao = cmd.CreateParameter();
            paramDescricao.ParameterName = "DS_MENU";
            paramDescricao.DbType = DbType.String;
            paramDescricao.Value = descricao;

            cmd.Parameters.Add(paramMenuSup);
            cmd.Parameters.Add(paramAssembly);
            cmd.Parameters.Add(paramBloqueio);
            cmd.Parameters.Add(paramClasse);
            cmd.Parameters.Add(paramDescricao);
            cmd.Parameters.Add(paramFuncao);
            cmd.Parameters.Add(paramSistema);

            cmd.CommandText = sql.ToString();

            cmd.ExecuteNonQuery();

            CloseConnection();

        }

        public void AlterarMenu(int codMenu, string descricao, int menuSuperior, string funcao, string modulo, string classe, string assembly, string bloqueio)
        {
            StringBuilder sql = new StringBuilder(); ;
            DataSet ds = new DataSet();

            OpenConnection();

            DbCommand cmd = cnn.CreateCommand();

            sql.Append(@"UPDATE TFWMENU
                          SET CD_MENUSUP = :CD_MENUSUP,
                          CD_FUNCAO = :CD_FUNCAO, 
                          CD_SISTEMA = :CD_SISTEMA, 
                          DS_ASSEMBLY = :DS_ASSEMBLY,
                          DS_CLASSE = :DS_CLASSE,
                          IN_BLOQUEIO = :IN_BLOQUEIO, 
                          DS_MENU = :DS_MENU
                        WHERE CD_MENU = :CD_MENU");


            
            DbParameter paramMenuSup = cmd.CreateParameter();
            paramMenuSup.ParameterName = "CD_MENUSUP";
            paramMenuSup.DbType = DbType.Int32;
            
            if (menuSuperior != 0)
                paramMenuSup.Value = menuSuperior;
            else
                paramMenuSup.Value = DBNull.Value;

            DbParameter paramMenu = cmd.CreateParameter();
            paramMenu.ParameterName = "CD_MENU";
            paramMenu.DbType = DbType.Int32;
            paramMenu.Value = codMenu;

            DbParameter paramFuncao = cmd.CreateParameter();
            paramFuncao.ParameterName = "CD_FUNCAO";
            paramFuncao.DbType = DbType.String;
            paramFuncao.Value = funcao;

            DbParameter paramSistema = cmd.CreateParameter();
            paramSistema.ParameterName = "CD_SISTEMA";
            paramSistema.DbType = DbType.String;
            paramSistema.Value = modulo;

            DbParameter paramAssembly = cmd.CreateParameter();
            paramAssembly.ParameterName = "DS_ASSEMBLY";
            paramAssembly.DbType = DbType.String;
            paramAssembly.Value = assembly;

            DbParameter paramClasse = cmd.CreateParameter();
            paramClasse.ParameterName = "DS_CLASSE";
            paramClasse.DbType = DbType.String;
            paramClasse.Value = classe;

            DbParameter paramBloqueio = cmd.CreateParameter();
            paramBloqueio.ParameterName = "IN_BLOQUEIO";
            paramBloqueio.DbType = DbType.String;
            paramBloqueio.Value = bloqueio;

            DbParameter paramDescricao = cmd.CreateParameter();
            paramDescricao.ParameterName = "DS_MENU";
            paramDescricao.DbType = DbType.String;
            paramDescricao.Value = descricao;

            cmd.Parameters.Add(paramMenuSup);
            cmd.Parameters.Add(paramMenu);
            cmd.Parameters.Add(paramAssembly);
            cmd.Parameters.Add(paramBloqueio);
            cmd.Parameters.Add(paramClasse);
            cmd.Parameters.Add(paramDescricao);
            cmd.Parameters.Add(paramFuncao);
            cmd.Parameters.Add(paramSistema);

            cmd.CommandText = sql.ToString();

            cmd.ExecuteNonQuery();

            CloseConnection();

        }

        public void ExcluirMenu(int codMenu)
        {
            StringBuilder sql = new StringBuilder(); ;
            DataSet ds = new DataSet();

            OpenConnection();

            DbCommand cmd = cnn.CreateCommand();

            sql.Append(@"DELETE TFWMENU
                        WHERE CD_MENU = :CD_MENU");


            DbParameter paramMenu = cmd.CreateParameter();
            paramMenu.ParameterName = "CD_MENU";
            paramMenu.DbType = DbType.Int32;
            paramMenu.Value = codMenu;

            cmd.Parameters.Add(paramMenu);

            cmd.CommandText = sql.ToString();

            cmd.ExecuteNonQuery();

            CloseConnection();

        }

        public DataSet BuscaFuncoes(string codModulo)
        {
            StringBuilder sql = new StringBuilder(); ;
            DataSet ds = new DataSet();

            OpenConnection();

            DbCommand cmd = cnn.CreateCommand();

            sql.Append(@"SELECT CD_FUNCAO, TO_CHAR(CD_FUNCAO) || ' - ' || NM_FUNCAO ""NM_FUNCAO"" FROM TGEFUNCAO WHERE CD_SISTEMA = '" + codModulo + "' AND IN_BLOQUEIO = 'N' ORDER BY NM_FUNCAO");

            cmd.CommandText = sql.ToString();

            IDbDataAdapter dataAdapter = new OracleDataAdapter();
            dataAdapter.SelectCommand = cmd;
            dataAdapter.Fill(ds);

            CloseConnection();

            return ds;

        }

    }
}
